from os import system
import pandas as pd
import openpyxl
from myTranslator import MyTranslator

def main():
    # 文件放在files目录
    inFileName='files/AOE4 Quick Sheet#20211130.xlsx'
    outFileName='files/帝国4数据快速查询表#20211130.xlsx'
    wordTableName='files/wordTable.xlsx'
    # 初始花单词表
    initWordTable(wordTableName)
    # 读取要翻译的文件
    df = pd.ExcelFile(inFileName)
    out_workbook=openpyxl.load_workbook(inFileName)
    # 逐表遍历
    for sheetName in df.sheet_names:
        eachDf = pd.read_excel(inFileName,sheet_name=sheetName,header=None)
        #print(out_workbook[sheetName]._images)
        #print(eachDf.shape)
        #print(eachDf)
        writeDataFrame(out_workbook[sheetName],1,1,eachDf)
    # 保存新文件
    out_workbook.save(outFileName)


def initWordTable(wordTableName):
    #WORD_TABLE是用来翻译的单词表
    global WORD_TABLE
    translator = MyTranslator(wordTableName)
    WORD_TABLE = translator.word_table

def doTranslate(val):
    #执行翻译,如果在索引中有目标词，取该行Chinese列的值
    if(val in WORD_TABLE.index):
        val=WORD_TABLE.at[val,'Chinese']
    return val

def writeDataFrame(sheet, start_row, start_col, df):
    i=start_row
    j=start_col
    for index,row in df.iterrows():
        for cell in row:
            if(pd.isnull(cell)): # NaN值跳过
                None
            else:
                cell=doTranslate(cell)
                sheet.cell(i,j).value=cell
            j=j+1
        j=start_col
        i=i+1
    return


if __name__=="__main__":
    main()